package com.njcky.utils.db;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.*;

/**
 * 基于Spring管理的连接池数据库工具类
 * 整合原DBHelper所有功能，使用Hikari连接池管理连接
 */
@Component
public class DBPool2DataSourceUtil {

    private final DBPool2Util dbPoolUtil;

    // 构造函数注入连接池配置类
    @Autowired
    public DBPool2DataSourceUtil(DBPool2Util dbPoolUtil) {
        this.dbPoolUtil = dbPoolUtil;
    }


    /**
     * 返回查询结果的(第一行)第一列
     */
    public Object NpgNormal_GetScalar(String sql) {
        Object value = null;
        // 从连接池获取连接，使用try-with-resources自动关闭资源
        try (Connection connection = dbPoolUtil.connectivityPublic().getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {

            ResultSetMetaData md = resultSet.getMetaData();
            int columnCount = md.getColumnCount();
            int rowCount = 0;

            while (resultSet.next()) {
                rowCount++;
                // 只取第一行第一列
                if (rowCount == 1 && columnCount >= 1) {
                    value = resultSet.getObject(1);
                    break; // 取到后直接退出
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return value;
    }


    /**
     * 返回查询结果集（基础版）
     */
    public List<Map<String, Object>> NpgNormal_GetListDic(String sql) {
        List<Map<String, Object>> resultList = new ArrayList<>();

        try (Connection connection = dbPoolUtil.connectivityPublic().getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet resultSet = ps.executeQuery()) {

            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (resultSet.next()) {
                Map<String, Object> rowMap = new HashMap<>(columnCount);
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object value = resultSet.getObject(i);
                    rowMap.put(columnName, value == null ? "" : value.toString());
                }
                resultList.add(rowMap);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultList;
    }


    /**
     * 带执行耗时统计的查询（针对路径查询优化）
     */
    public List<Map<String, Object>> NpgNormal_GetListDic_getBsmDateParallel(String sql) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        try (Connection connection = dbPoolUtil.connectivityPublic().getConnection()) {
            try (PreparedStatement ps = connection.prepareStatement(sql);
                 ResultSet resultSet = ps.executeQuery()) {
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                while (resultSet.next()) {
                    Map<String, Object> rowMap = new HashMap<>(columnCount);
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnLabel(i);
                        Object value = resultSet.getObject(i);
                        rowMap.put(columnName, value == null ? "" : value.toString());
                    }
                    resultList.add(rowMap);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultList;
    }



    public  List<Map<String, Object>> connectivity(String sql) {
        Connection connection = null;
        PreparedStatement pstmt = null; // 使用JDBC预编译Statement
        ResultSet resultSet = null;
        List<Map<String, Object>> resultlist = new ArrayList<>();
        try {
            // 1. 加载驱动并建立连接
            Class.forName("org.postgresql.Driver");
            long connectStart = System.currentTimeMillis();
            connection = dbPoolUtil.connectivityPublic().getConnection();
            long connectCost = System.currentTimeMillis() - connectStart;
            System.out.printf("连接建立耗时: %d ms%n", connectCost);
            // 2. 使用JDBC预编译（自动处理PREPARE逻辑）
            long sqlExecuteStart = System.currentTimeMillis();
            pstmt = connection.prepareStatement(sql); // 驱动自动预编译SQL
            resultSet = pstmt.executeQuery(); // 执行预编译后的查询
            // 计算SQL执行耗时
            long sqlExecuteCost = System.currentTimeMillis() - sqlExecuteStart;
            System.out.printf("123123数据库执行SQL耗时（纯执行，不含传输）: %d ms%n", sqlExecuteCost);

            // 3. 处理结果集
            long resultHandleStart = System.currentTimeMillis();
            ResultSetMetaData md = resultSet.getMetaData();
            int columnCount = md.getColumnCount();
            while (resultSet.next()) {
                Map<String, Object> dic = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = md.getColumnLabel(i);
                    Object value = resultSet.getObject(i);
                    dic.put(columnName, value == null ? "" : value.toString());
                }
                resultlist.add(dic);
            }
            long resultHandleCost = System.currentTimeMillis() - resultHandleStart;
            System.out.printf("结果集传输+处理耗时: %d ms%n", resultHandleCost);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (resultSet != null) resultSet.close();
                if (pstmt != null) pstmt.close(); // 关闭预编译Statement
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultlist;
    }




    // 核心参数化查询实现
    public static List<Map<String, Object>> NpgNormal_GetListDicWithParams(String sql, String pgUrl, String pgUserName, String pgPassword, List<Object> params) {
        Connection connection = null;
        PreparedStatement preparedStatement = null; // 使用PreparedStatement替代Statement
        ResultSet resultSet = null;
        List<Map<String, Object>> resultlist = new ArrayList<Map<String, Object>>();
        try {
            Class.forName("org.postgresql.Driver");
            // 连接数据库
            connection = DriverManager.getConnection(pgUrl, pgUserName, pgPassword);
            // 使用PreparedStatement预处理SQL，防止注入并提高效率
            preparedStatement = connection.prepareStatement(sql);
            // 设置参数
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    // PreparedStatement参数索引从1开始
                    preparedStatement.setObject(i + 1, params.get(i));
                }
            }

            // 执行查询
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData md = resultSet.getMetaData();
            int columnCount = md.getColumnCount();

            while (resultSet.next()) {
                Map<String, Object> dic = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    Object value = resultSet.getObject(i);
                    // 处理null值，转为空字符串
                    dic.put(md.getColumnLabel(i), value == null ? "" : value.toString());
                }
                resultlist.add(dic);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源，使用try-with-resources或工具类会更优雅
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultlist;
    }






    /**
     * 带参数的查询（防止SQL注入，推荐使用）
     */
    public List<Map<String, Object>> NpgNormal_GetListDic(String sql, Object... params) {
        return NpgNormal_GetListDicWithParams(sql, params != null ? Arrays.asList(params) : new ArrayList<>());
    }


    /**
     * 核心参数化查询实现
     */
    public List<Map<String, Object>> NpgNormal_GetListDicWithParams(String sql, List<Object> params) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        try (Connection connection = dbPoolUtil.connectivityPublic().getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            // 设置参数（索引从1开始）
            // 修复：检查params是否为null且不为空列表
            if (params != null && params.size() > 0) {
                for (int i = 0; i < params.size(); i++) {
                    ps.setObject(i + 1, params.get(i));
                }
            }
            // 执行查询
            try (ResultSet resultSet = ps.executeQuery()) {
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                while (resultSet.next()) {
                    Map<String, Object> rowMap = new HashMap<>(columnCount);
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnLabel(i);
                        Object value = resultSet.getObject(i);
                        rowMap.put(columnName, value == null ? "" : value.toString());
                    }
                    resultList.add(rowMap);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultList;
    }


    /**
     * 返回查询结果的第一行
     */
    public Map<String, Object> NpgNormal_GetListDicNo1(String sql) {
        Map<String, Object> resultMap = new HashMap<>();

        try (Connection connection = dbPoolUtil.connectivityPublic().getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet resultSet = ps.executeQuery()) {

            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 只取第一行
            if (resultSet.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object value = resultSet.getObject(i);
                    resultMap.put(columnName, value == null ? "" : value.toString());
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultMap;
    }


    /**
     * 执行增删改操作，返回受影响行数
     */
    public int NpgNormal_ExecutorNonQuery(String sql) {
        int affectRows = 0;

        try (Connection connection = dbPoolUtil.connectivityPublic().getConnection();
             Statement statement = connection.createStatement()) {

            affectRows = statement.executeUpdate(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return affectRows;
    }


    /**
     * 执行带参数的增删改操作
     */
    public int NpgNormal_ExecutorNonQuery(String sql, Object... params) {
        int affectRows = 0;

        try (Connection connection = dbPoolUtil.connectivityPublic().getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {

            // 设置参数
            // 修复：检查params是否为null且不为空列表
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }

            affectRows = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return affectRows;
    }


    /**
     * 获取查询结果总数（需SQL返回别名"total"的字段）
     */
    public int NpgNormal_QueryCount(String sql) {
        int total = 0;

        try (Connection connection = dbPoolUtil.connectivityPublic().getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet resultSet = ps.executeQuery()) {

            // 计数查询通常只有一行结果
            if (resultSet.next()) {
                total = resultSet.getInt("total"); // 依赖SQL中定义的别名"total"
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }



    public static List<Map<String, Object>> NpgNormal_GetListDic_getBsmDateParallel(String sql, String pgUrl, String pgUserName, String pgPassword) {
        Connection connection = null;
        PreparedStatement pstmt = null; // 使用JDBC预编译Statement
        ResultSet resultSet = null;
        List<Map<String, Object>> resultlist = new ArrayList<>();
        try {
            // 1. 加载驱动并建立连接
            Class.forName("org.postgresql.Driver");
            long connectStart = System.currentTimeMillis();
            connection = DriverManager.getConnection(pgUrl, pgUserName, pgPassword);
            long connectCost = System.currentTimeMillis() - connectStart;
            System.out.printf("连接建立耗时: %d ms%n", connectCost);

            // 2. 使用JDBC预编译（自动处理PREPARE逻辑）
            long sqlExecuteStart = System.currentTimeMillis();
            pstmt = connection.prepareStatement(sql); // 驱动自动预编译SQL
            resultSet = pstmt.executeQuery(); // 执行预编译后的查询

            // 计算SQL执行耗时
            long sqlExecuteCost = System.currentTimeMillis() - sqlExecuteStart;
            System.out.printf("123123数据库执行SQL耗时（纯执行，不含传输）: %d ms%n", sqlExecuteCost);

            // 3. 处理结果集
            long resultHandleStart = System.currentTimeMillis();
            ResultSetMetaData md = resultSet.getMetaData();
            int columnCount = md.getColumnCount();
            while (resultSet.next()) {
                Map<String, Object> dic = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = md.getColumnLabel(i);
                    Object value = resultSet.getObject(i);
                    dic.put(columnName, value == null ? "" : value.toString());
                }
                resultlist.add(dic);
            }
            long resultHandleCost = System.currentTimeMillis() - resultHandleStart;
            System.out.printf("结果集传输+处理耗时: %d ms%n", resultHandleCost);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (resultSet != null) resultSet.close();
                if (pstmt != null) pstmt.close(); // 关闭预编译Statement
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultlist;
    }
}
